﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class PhieuThuePhongDAO:AbstractDAO
    {

        public static List<PhieuThuePhongDTO> layDSPhieuThue()
        {
            OleDbConnection ketNoi = null;
            List<PhieuThuePhongDTO> dsPhieuThue = new List<PhieuThuePhongDTO>();
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "SELECT MaPhieuThue,NgayBatDauThue,P.MaPhong,DaThanhToan,P.TenPhong,LP.MaLoaiPhong,LP.TenLoaiPhong FROM PhieuThuePhong PT,Phong P,LoaiPhong LP WHERE P.MaPhong=PT.MaPhong AND LP.MaLoaiPhong=P.MaLoaiPhong";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    PhieuThuePhongDTO phieuThue = new PhieuThuePhongDTO();
                    phieuThue.MaPhieuThue = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        phieuThue.NgayBatDauThue = boDoc.GetDateTime(1);
                    if (!boDoc.IsDBNull(3))
                        phieuThue.DaThanhToan = boDoc.GetInt32(3);


                    PhongDTO phong = new PhongDTO();
                    phong.MaPhong = boDoc.GetInt32(2);
                    phong.TenPhong = boDoc.GetString(4);

                    LoaiPhongDTO loaiPhong = new LoaiPhongDTO();
                    loaiPhong.MaLoaiPhong = boDoc.GetInt32(5);
                    loaiPhong.TenLoaiPhong = boDoc.GetString(6);

                    phong.LoaiPhong = loaiPhong;
                    

                    phieuThue.Phong = phong;

                    dsPhieuThue.Add(phieuThue);
                }

            }
            catch
            {
                dsPhieuThue = new List<PhieuThuePhongDTO>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsPhieuThue;
        }

        public static bool capNhat(PhieuThuePhongDTO phieuThue)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "UPDATE PhieuThuePhong SET NgayBatDauThue=@NgayBatDauThue,MaPhong=@MaPhong WHERE MaPhieuThue=@MaPhieuThue";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);
                OleDbParameter thamSo;

                thamSo = new OleDbParameter("@NgayBatDauThue", OleDbType.Date);
                thamSo.Value = phieuThue.NgayBatDauThue;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaPhong", OleDbType.Integer);
                thamSo.Value = phieuThue.Phong.MaPhong;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaPhieuThue", OleDbType.Integer);
                thamSo.Value = phieuThue.MaPhieuThue;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;

        }
        public static bool xoa(long maPhieuThue)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "DELETE FROM PhieuThuePhong WHERE MaPhieuThue=@MaPhieuThue";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@MaPhieuThue", OleDbType.Integer);
                thamSo.Value = maPhieuThue;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }
        public static bool them(PhieuThuePhongDTO phieuThue)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "INSERT INTO PhieuThuePhong(NgayBatDauThue,MaPhong)";
                chuoiLenh += " VALUES (@NgayBatDauThue,@MaPhong)";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@NgayBatDauThue", OleDbType.Date);
                thamSo.Value = phieuThue.NgayBatDauThue;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaPhong", OleDbType.Integer);
                thamSo.Value = phieuThue.Phong.MaPhong;
                lenh.Parameters.Add(thamSo);



                lenh.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }
        public static long layMaPhieuThueLonNhat()
        {
            OleDbConnection ketNoi = null;
            long maPhieuThueMax = 0;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "SELECT MAX(MaPhieuThue) FROM PhieuThuePhong ";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    maPhieuThueMax = boDoc.GetInt32(0);

                }

            }
            catch
            {

            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return maPhieuThueMax;
        }
        public static bool capNhatDaThanhToan(long maPhieuThue)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "UPDATE PhieuThuePhong SET DaThanhToan=@DaThanhToan WHERE MaPhieuThue=@MaPhieuThue";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);
                OleDbParameter thamSo;

                thamSo = new OleDbParameter("@DaThanhToan", OleDbType.Integer);
                thamSo.Value = 1;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaPhieuThue", OleDbType.Integer);
                thamSo.Value = maPhieuThue;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;

        }
    }
}
